﻿using System;
using System.Collections.Generic;
using System.Text;
using AspNetCs.Model;
using System.Data;
using AspNetCs.Common;
using AspNetCs.IDAL;

namespace AspNetCs.SQLServerDAL
{
    public class FindFamilyDAL : IFindFamily
    {
        /// <summary>
        /// 添加寻亲信息
        /// </summary>
        public int AddFamilyInfo(FindFamilyModel model)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[30];

            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@ItemName";
            paras[0].objValue = model.ItemName;

            paras[1].dbType = SqlDbType.VarChar;
            paras[1].parName = "@PreName";
            paras[1].objValue = model.PreName;

            paras[2].dbType = SqlDbType.VarChar;
            paras[2].parName = "@PostfixName";
            paras[2].objValue = model.PostfixName;

            paras[3].dbType = SqlDbType.VarChar;
            paras[3].parName = "@OtherName";
            paras[3].objValue = model.OtherName;

            paras[4].dbType = SqlDbType.VarChar;
            paras[4].parName = "@Sex";
            paras[4].objValue = model.Sex;

            paras[5].dbType = SqlDbType.Int;
            paras[5].parName = "@Age";
            paras[5].objValue = model.Age;

            paras[6].dbType = SqlDbType.VarChar;
            paras[6].parName = "@Birthday";
            paras[6].objValue = model.Birthday;

            paras[7].dbType = SqlDbType.VarChar;
            paras[7].parName = "@DepartBirthday";
            paras[7].objValue = model.DepartBirthday;

            paras[8].dbType = SqlDbType.VarChar;
            paras[8].parName = "@BirthType";
            paras[8].objValue = model.BirthType;

            paras[9].dbType = SqlDbType.VarChar;
            paras[9].parName = "@Nation";
            paras[9].objValue = model.Nation;


            paras[10].dbType = SqlDbType.VarChar;
            paras[10].parName = "@BirthPlace";
            paras[10].objValue = model.BirthPlace;

            paras[11].dbType = SqlDbType.VarChar;
            paras[11].parName = "@Country";
            paras[11].objValue = model.Country;

            paras[12].dbType = SqlDbType.VarChar;
            paras[12].parName = "@Work";
            paras[12].objValue = model.Work;

            paras[13].dbType = SqlDbType.VarChar;
            paras[13].parName = "@LostPlace";
            paras[13].objValue = model.LostPlace;

            paras[14].dbType = SqlDbType.VarChar;
            paras[14].parName = "@Phone";
            paras[14].objValue = model.Phone;

            paras[15].dbType = SqlDbType.VarChar;
            paras[15].parName = "@Telephone";
            paras[15].objValue = model.Telephone;

            paras[16].dbType = SqlDbType.VarChar;
            paras[16].parName = "@Email";
            paras[16].objValue = model.Email;

            paras[17].dbType = SqlDbType.VarChar;
            paras[17].parName = "@QQ";
            paras[17].objValue = model.Qq;


            paras[18].dbType = SqlDbType.VarChar;
            paras[18].parName = "@ContactAddress";
            paras[18].objValue = model.ContactAddress;

            paras[19].dbType = SqlDbType.VarChar;
            paras[19].parName = "@LostPerInfo";
            paras[19].objValue = model.LostPerInfo;

            paras[20].dbType = SqlDbType.DateTime;
            paras[20].parName = "@RegisterTime";
            paras[20].objValue = model.RegisterTime;

            paras[21].dbType = SqlDbType.Int;
            paras[21].parName = "@CreateId";
            paras[21].objValue = model.CreateId;

            paras[22].dbType = SqlDbType.Int;
            paras[22].parName = "@IsCheck";
            paras[22].objValue = model.IsCheck;

            paras[23].dbType = SqlDbType.Int;
            paras[23].parName = "@IsDel";
            paras[23].objValue = model.IsDel;

            paras[24].dbType = SqlDbType.VarChar;
            paras[24].parName = "@Photo";
            paras[24].objValue = model.Photo;


            paras[25].dbType = SqlDbType.Int;
            paras[25].parName = "@AcceeCount";
            paras[25].objValue = model.AcceeCount;

            paras[26].dbType = SqlDbType.Int;
            paras[26].parName = "@TempleId";
            paras[26].objValue = model.TempleId;

            paras[27].dbType = SqlDbType.Int;
            paras[27].parName = "@MusicId";
            paras[27].objValue = model.MusicId;

            paras[28].dbType = SqlDbType.Int;
            paras[28].parName = "@IsIntro";
            paras[28].objValue = model.IsIntro;

            paras[29].dbType = SqlDbType.VarChar;
            paras[29].parName = "@Title";
            paras[29].objValue = model.Title;


            string cmdText = "insert into FindFamily values ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29})";
            cmdText = string.Format(cmdText, paras[0].parName, paras[1].parName, paras[2].parName, paras[3].parName, paras[4].parName, paras[5].parName, paras[6].parName, paras[7].parName, paras[8].parName, paras[9].parName, paras[10].parName, paras[11].parName, paras[12].parName, paras[13].parName, paras[14].parName, paras[15].parName, paras[16].parName, paras[17].parName, paras[18].parName, paras[19].parName, paras[20].parName, paras[21].parName, paras[22].parName, paras[23].parName, paras[24].parName, paras[25].parName, paras[26].parName, paras[27].parName, paras[28].parName, paras[29].parName);
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;
        }

        /// <summary>
        /// 删除寻亲信息(逻辑删除)
        /// </summary>
        /// <param name="id"></param>
        public int HiddenFamilyInfo(int id)
        {
            int count = 0;
            string cmdText = "update FindFamily set IsDel=1 where Id=@Id";
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@Id";
            paras[0].objValue = id;
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;
        }

        /// <summary>
        /// 删除寻亲信息(物理删除)
        /// </summary>
        /// <param name="id"></param>
        public int DeleteFamilyInfo(int id)
        {
            int count = 0;
            string cmdText = "delete from FindFamily where Id=@Id";
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@Id";
            paras[0].objValue = id;
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;
        }

        /// <summary>
        /// 查询用户添加的寻亲信息
        /// </summary>
        /// <param name="CreateId">创建者Id</param>
        /// <param name="CheckedOrNo">查看未审核通过(0)、审核通过(1)、所有的(2)</param>
        /// <param name="QueryPropertys">需要查询的字段如 Id,Name,如需查询所有的字段输入</param>
        /// <returns></returns>
        public DataTable SearchUserCreateFamilyInfo(string QueryPropertys, int CreateId, int CheckedOrNo)
        {
            DataTable dataTable = new DataTable();
            string strWhere = "";
            switch (CheckedOrNo)
            {
                case 0:
                    strWhere = " and IsCheck=" + CheckedOrNo;
                    break;
                case 1:
                    strWhere = " and IsCheck=" + CheckedOrNo;
                    break;
                default:
                    strWhere = "";
                    break;
            }
            string cmdText = "select " + QueryPropertys + " from FindFamily where CreateId=@CreateId and IsDel=0 " + strWhere + " order by RegisterTime desc";
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@CreateId";
            paras[0].objValue = CreateId;

            db dbclass = new db();
            dataTable = dbclass.GetSql_DataTable(cmdText, paras);
            return dataTable;
        }

        /// <summary>
        /// 修改寻亲信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateFamilyInfo(FindFamilyModel model)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[30];

            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@ItemName";
            paras[0].objValue = model.ItemName;

            paras[1].dbType = SqlDbType.VarChar;
            paras[1].parName = "@PreName";
            paras[1].objValue = model.PreName;

            paras[2].dbType = SqlDbType.VarChar;
            paras[2].parName = "@PostfixName";
            paras[2].objValue = model.PostfixName;

            paras[3].dbType = SqlDbType.VarChar;
            paras[3].parName = "@OtherName";
            paras[3].objValue = model.OtherName;

            paras[4].dbType = SqlDbType.VarChar;
            paras[4].parName = "@Sex";
            paras[4].objValue = model.Sex;

            paras[5].dbType = SqlDbType.Int;
            paras[5].parName = "@Age";
            paras[5].objValue = model.Age;

            paras[6].dbType = SqlDbType.VarChar;
            paras[6].parName = "@Birthday";
            paras[6].objValue = model.Birthday;

            paras[7].dbType = SqlDbType.VarChar;
            paras[7].parName = "@DepartBirthday";
            paras[7].objValue = model.DepartBirthday;

            paras[8].dbType = SqlDbType.VarChar;
            paras[8].parName = "@BirthType";
            paras[8].objValue = model.BirthType;

            paras[9].dbType = SqlDbType.VarChar;
            paras[9].parName = "@Nation";
            paras[9].objValue = model.Nation;


            paras[10].dbType = SqlDbType.VarChar;
            paras[10].parName = "@BirthPlace";
            paras[10].objValue = model.BirthPlace;

            paras[11].dbType = SqlDbType.VarChar;
            paras[11].parName = "@Country";
            paras[11].objValue = model.Country;

            paras[12].dbType = SqlDbType.VarChar;
            paras[12].parName = "@Work";
            paras[12].objValue = model.Work;

            paras[13].dbType = SqlDbType.VarChar;
            paras[13].parName = "@LostPlace";
            paras[13].objValue = model.LostPlace;

            paras[14].dbType = SqlDbType.VarChar;
            paras[14].parName = "@Phone";
            paras[14].objValue = model.Phone;

            paras[15].dbType = SqlDbType.VarChar;
            paras[15].parName = "@Telephone";
            paras[15].objValue = model.Telephone;

            paras[16].dbType = SqlDbType.VarChar;
            paras[16].parName = "@Email";
            paras[16].objValue = model.Email;

            paras[17].dbType = SqlDbType.VarChar;
            paras[17].parName = "@QQ";
            paras[17].objValue = model.Qq;


            paras[18].dbType = SqlDbType.VarChar;
            paras[18].parName = "@ContactAddress";
            paras[18].objValue = model.ContactAddress;

            paras[19].dbType = SqlDbType.VarChar;
            paras[19].parName = "@LostPerInfo";
            paras[19].objValue = model.LostPerInfo;

            paras[20].dbType = SqlDbType.DateTime;
            paras[20].parName = "@RegisterTime";
            paras[20].objValue = model.RegisterTime;

            paras[21].dbType = SqlDbType.Int;
            paras[21].parName = "@CreateId";
            paras[21].objValue = model.CreateId;

            paras[22].dbType = SqlDbType.Int;
            paras[22].parName = "@IsCheck";
            paras[22].objValue = model.IsCheck;

            paras[23].dbType = SqlDbType.Int;
            paras[23].parName = "@IsDel";
            paras[23].objValue = model.IsDel;

            paras[24].dbType = SqlDbType.VarChar;
            paras[24].parName = "@Photo";
            paras[24].objValue = model.Photo;


            paras[25].dbType = SqlDbType.Int;
            paras[25].parName = "@AcceeCount";
            paras[25].objValue = model.AcceeCount;

            paras[26].dbType = SqlDbType.Int;
            paras[26].parName = "@TempleId";
            paras[26].objValue = model.TempleId;

            paras[27].dbType = SqlDbType.Int;
            paras[27].parName = "@MusicId";
            paras[27].objValue = model.MusicId;

            paras[28].dbType = SqlDbType.Int;
            paras[28].parName = "@Id";
            paras[28].objValue = model.Id;

            paras[29].dbType = SqlDbType.VarChar;
            paras[29].parName = "@Title";
            paras[29].objValue = model.Title;

            string cmdText = "update FindFamily set ItemName={0},PreName={1},PostfixName={2},OtherName={3},Sex={4},Age={5},Birthday={6},DepartBirthday={7},BirthType={8},Nation={9},BirthPlace={10},Country={11},Work={12},LostPlace={13},Phone={14},Telephone={15},Email={16},QQ={17},ContactAddress={18},LostPerInfo={19},RegisterTime={20},CreateId={21},IsCheck={22},IsDel={23},Photo={24},AcceeCount={25},TempleId={26},MusicId={27},Title={29} where Id={28}";
            cmdText = string.Format(cmdText, paras[0].parName, paras[1].parName, paras[2].parName, paras[3].parName, paras[4].parName, paras[5].parName, paras[6].parName, paras[7].parName, paras[8].parName, paras[9].parName, paras[10].parName, paras[11].parName, paras[12].parName, paras[13].parName, paras[14].parName, paras[15].parName, paras[16].parName, paras[17].parName, paras[18].parName, paras[19].parName, paras[20].parName, paras[21].parName, paras[22].parName, paras[23].parName, paras[24].parName, paras[25].parName, paras[26].parName, paras[27].parName, paras[28].parName, paras[29].parName);
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;
        }

        /// <summary>
        /// 通过ID查询寻亲信息
        /// <param name="QueryPropertys">需要查询的字段如 Id,Name,如需查询所有的字段输入</param> 
        /// <param name="CheckedOrNo">编号</param>
        /// <returns></returns>
        public DataTable SearchFamilyInfoById(string QueryPropertys, int Id)
        {
            DataTable dataTable = new DataTable();
            string cmdText = "select " + QueryPropertys + " from FindFamily where Id=@Id and IsDel=0";
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@Id";
            paras[0].objValue = Id;

            db dbclass = new db();
            dataTable = dbclass.GetSql_DataTable(cmdText, paras);
            return dataTable;
        }
        /// <summary>
        /// 设置推荐或者取消推荐
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="CancleOrIntro">1表示设置推荐 0表示取消推荐</param>
        /// <returns></returns>
        public int SetIntro(int Id, int CancleOrIntro)
        {
            int count = 0;
            string cmdText = "update FindFamily set IsIntro=@IsIntro where id=@id";
            db.ParameterArry[] paras = new db.ParameterArry[2];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@IsIntro";
            paras[0].objValue = CancleOrIntro;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@id";
            paras[1].objValue = Id;

            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);

            return count;
        }

        /// <summary>
        /// 审核或者取消审核
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="CancleOrIntro">1表示审核 0表示取消审核</param>
        /// <returns></returns>
        public int SetCheck(int Id, int CancleOrIntro)
        {
            int count = 0;
            string cmdText = "update FindFamily set IsCheck=@IsCheck where id=@id";
            db.ParameterArry[] paras = new db.ParameterArry[2];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@IsCheck";
            paras[0].objValue = CancleOrIntro;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@id";
            paras[1].objValue = Id;

            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);

            return count;
        }


        /// <summary>
        /// 设置背景音乐
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="MusicId"></param>
        /// <returns></returns>
        public int SetMusic(int Id, int MusicId)
        {
            string cmdText = "update FindFamily set MusicId=@MusicId where Id=@ID";
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[2];

            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@MusicId";
            paras[0].objValue = MusicId;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@ID";
            paras[1].objValue = Id;

            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;

        }


        public void filterUpdate(string Upfilter, string filter)
        {
            db Conn = new db();
            if (filter + "" != "")
            {
                filter = "  where " + filter;
            }
            Conn.Execute_Noget("update FindFamily Set " + Upfilter + " " + filter);
            Conn.close();
        }


        public FindFamilyModel GetModel(DataRow dr)
        {
            FindFamilyModel Md = new FindFamilyModel(); ;
            try
            {
                Md.Id = StringHelper.isNumber_int(dr["id"]);
                Md.ItemName = StringHelper.isNumber_int(dr["ItemName"]);
                if (dr["PreName"] != null) { Md.PreName = dr["PreName"].ToString(); }
                if (dr["PostfixName"] != null) { Md.PostfixName = dr["PostfixName"].ToString(); }
                if (dr["OtherName"] != null) { Md.OtherName = dr["OtherName"].ToString(); }
                if (dr["Sex"] != null) { Md.Sex = dr["Sex"].ToString(); }
                Md.Age = StringHelper.isNumber_int(dr["Age"]);
                if (dr["Birthday"] != null) { Md.Birthday = dr["Birthday"].ToString(); }
                if (dr["DepartBirthday"] != null) { Md.DepartBirthday = dr["DepartBirthday"].ToString(); }
                if (dr["BirthType"] != null) { Md.BirthType = dr["BirthType"].ToString(); }
                if (dr["Nation"] != null) { Md.Nation = dr["Nation"].ToString(); }
                if (dr["BirthPlace"] != null) { Md.BirthPlace = dr["BirthPlace"].ToString(); }
                if (dr["Country"] != null) { Md.Country = dr["Country"].ToString(); }
                if (dr["Work"] != null) { Md.Work = dr["Work"].ToString(); }
                if (dr["LostPlace"] != null) { Md.LostPlace = dr["LostPlace"].ToString(); }
                if (dr["Phone"] != null) { Md.Phone = dr["Phone"].ToString(); }
                if (dr["Telephone"] != null) { Md.Telephone = dr["Telephone"].ToString(); }
                if (dr["Email"] != null) { Md.Email = dr["Email"].ToString(); }
                if (dr["QQ"] != null) { Md.Qq = dr["QQ"].ToString(); }
                if (dr["ContactAddress"] != null) { Md.ContactAddress = dr["ContactAddress"].ToString(); }
                if (dr["LostPerInfo"] != null) { Md.LostPerInfo = dr["LostPerInfo"].ToString(); }
                if (dr["RegisterTime"] != null) { Md.RegisterTime = StringHelper.isDatetime(dr["RegisterTime"].ToString()); }
                Md.CreateId = StringHelper.isNumber_int(dr["CreateId"]);
                Md.IsCheck = StringHelper.isNumber_int(dr["IsCheck"]);
                Md.IsDel = StringHelper.isNumber_int(dr["IsDel"]);
                if (dr["Photo"] != null) { Md.Photo = dr["Photo"].ToString(); }
                if (dr["Title"] != null) { Md.Title = dr["Title"].ToString(); }
                Md.AcceeCount = StringHelper.isNumber_int(dr["AcceeCount"]);
                Md.TempleId = StringHelper.isNumber_int(dr["TempleId"]);
                Md.MusicId = StringHelper.isNumber_int(dr["MusicId"]);
                Md.IsIntro = StringHelper.isNumber_int(dr["IsIntro"]);


            }
            catch
            {
            }
            return Md;


        }



        public List<FindFamilyModel> GetList(string filter)
        {
            List<FindFamilyModel> lt = new List<FindFamilyModel>();
            db Conn = new db();
            if (filter != "")
            {
                filter = " Where " + filter;
            }
            DataTable dt = Conn.GetSql_DataTable("Select * from FindFamily " + filter);
            foreach (DataRow dr in dt.Rows)
            {
                lt.Add(GetModel(dr));
            }
            return lt;
        }
    }
}
